﻿using System;
using System.Collections.Generic;
using System.Text;

namespace HotelManagement.Common.SQL
{
    public static class SqlFile
    {
        public static readonly string SQL_LOGIN_AUTHORIZATION = @"SELECT D_USER.ID FROM D_USER 
                                                                WHERE D_USER.USERNAME = @name AND D_USER.PASSWORD = @password ";

        public static readonly string SQL_SEARCH_GOODS_BYNAME = @"SELECT GOODS.ID, GOODS.NAME, GOODS.WARINGCOUNT, GOODS.COUNT, GOODS.PURCHASEPRICE, GOODS.SALEPRICE, GOODS.DESCRIPTION
                                                            FROM GOODS
                                                            WHERE GOODS.NAME = @name ";

        public static readonly string SQL_SEARCH_GOODS = @"SELECT GOODS.ID, GOODS.NAME, GOODS.WARINGCOUNT, GOODS.COUNT, GOODS.PURCHASEPRICE, GOODS.SALEPRICE, GOODS.DESCRIPTION
                                                            FROM GOODS ";

        public static readonly string SQL_SEARCH_GOODS_NAME = @"SELECT GOODS.NAME
                                                            FROM GOODS ";

        public static readonly string SQL_PURCHASE_GOODS = @"UPDATE GOODS SET GOODS.COUNT = GOODS.COUNT + ?
                                                            WHERE GOODS.ID = @id ";

        public static readonly string SQL_SALE_GOODS = @"UPDATE GOODS SET GOODS.COUNT = GOODS.COUNT - ?
                                                            WHERE GOODS.ID = @id ";

        public static readonly string SQL_UPDATE_GOODS = @"UPDATE GOODS SET GOODS.NAME = ?, GOODS.PURCHASEPRICE = ?,GOODS.SALEPRICE = ?,GOODS.WARINGCOUNT = ?
                                                            WHERE GOODS.ID = @id ";

        public static readonly string SQL_ADD_GOODS = @"INSERT INTO GOODS(NAME, PURCHASEPRICE, SALEPRICE, WARINGCOUNT) 
                                                        VALUES (?, ?, ?, ?)  ";

        public static readonly string SQL_DELETE_GOODS = @"DELETE FROM GOODS WHERE GOODS.ID = @id ";

        public static readonly string SQL_SEARCH_WARN_GOODS = @"SELECT GOODS.ID, GOODS.NAME, GOODS.WARINGCOUNT, GOODS.COUNT, GOODS.PURCHASEPRICE, GOODS.SALEPRICE
                                                            FROM GOODS
                                                            WHERE GOODS.COUNT <= GOODS.WARINGCOUNT ";

        public static readonly string SQL_SEARCH_USER_INFO = @"SELECT D_USER.ID, D_USER.USERNAME, D_USER.PASSWORD
                                                            FROM D_USER ";

        public static readonly string SQL_SEARCH_USER_INFO_BYNAME = @"SELECT D_USER.ID, D_USER.USERNAME, D_USER.PASSWORD
                                                            FROM D_USER
                                                            WHERE D_USER.NAME = @name ";

        public static readonly string SQL_UPDATE_USER = @"UPDATE D_USER SET D_USER.USERNAME = ?,D_USER.PASSWORD = ?
                                                            WHERE D_USER.ID = @id ";

        public static readonly string SQL_ADD_USER = @"INSERT INTO D_USER (USERNAME, [PASSWORD]) 
                                                        VALUES (?, ?)  ";

        public static readonly string SQL_DELETE_USER = @"DELETE FROM D_USER WHERE D_USER.ID = @id ";

        public static readonly string SQL_ADD_TRADE = @"INSERT INTO TRADE([TYPE],[GOODS_ID],[GOODS_NAME],[COUNT],[COST],[DATE])
                                                        VALUES(?, ?, ?, ?, ?, ?)";

        public static readonly string SQL_SEARCH_TRADE = @" SELECT TRADE.[TYPE], TRADE.GOODS_ID, TRADE.GOODS_NAME, TRADE.[COUNT], TRADE.COST, TRADE.DATE
                                                        FROM TRADE
                                                        ";
    }

}
